package ch.zbw.pra.sysventory.domain.dbhandling;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import ch.zbw.pra.sysventory.domain.dbconnector.IDBConnector;
import ch.zbw.pra.sysventory.domain.model.Computer;
import ch.zbw.pra.sysventory.domain.model.Nic;
import ch.zbw.pra.sysventory.domain.model.OperatingSystem;
import ch.zbw.pra.sysventory.domain.model.Printer;
import ch.zbw.pra.sysventory.domain.model.Processor;
import ch.zbw.pra.sysventory.domain.model.Scanversion;
import ch.zbw.pra.sysventory.domain.model.Software;
import ch.zbw.pra.sysventory.domain.model.VendorSoftware;
import ch.zbw.pra.sysventory.domain.util.MyLogger;

public class InsertScanversionIntoDB {

	private static final org.slf4j.Logger log = org.slf4j.LoggerFactory
			.getLogger(InsertScanversionIntoDB.class);

	private IDBConnector dbconnector;
	private StringBuffer sb;

	public InsertScanversionIntoDB(IDBConnector connector) {
		this.dbconnector = connector;

	}

	public void insertScanversionIntoDB(Scanversion scanversion) {
		this.dbconnector.setAutocommit(false);
		this.sb = new StringBuffer();
		String hostNameComputer = scanversion.getComputer().getHostNameComputer();
		int numberOfScanversion = getNumberOfLatestScanversionFromDB(hostNameComputer) + 1;

		Timestamp timestampScanversion = scanversion.getTimestampScanversion();
		this.sb.append("INSERT INTO scanversion "
				+ "(numberOfScanversion, timestampScanversion) VALUES " + "(" + numberOfScanversion
				+ ", '" + timestampScanversion + "');");
		this.dbconnector.updateQuery(this.sb.toString());
		int idScanversion = this.dbconnector.getLastInsertedId();

		Computer computer = scanversion.getComputer();
		OperatingSystem os = computer.getOs();
		Processor processor = computer.getProcessor();
		List<Nic> nics = computer.getNics();
		List<Software> softwares = computer.getSoftwares();
		List<Printer> printers = computer.getPrinters();

		int idOs = insertOSIntoDB(os);
		int idComputer = insertComputerIntoDB(computer, idOs);
		insertScanversionHasComputer(idScanversion, idComputer);
		int idProcessor = insertProcessor(processor);
		insertComputerHasProcessor(idProcessor, idComputer);
		insertNics(nics, idComputer);
		insertSoftwares(softwares, idComputer);
		insertPrinters(printers, idComputer);
		this.dbconnector.commit();
		this.dbconnector.setAutocommit(true);

	}

	private int getNumberOfLatestScanversionFromDB(String hostNameComputer) {
		int numberOfScanversion = 0;
		String query = "SELECT numberOfScanversion " + "FROM scanversion "
				+ "JOIN scanversion_has_computer ON idScanversion=scanversion_idScanversion "
				+ "JOIN computer ON computer_idComputer = idComputer "
				+ "WHERE hostNameComputer = '" + hostNameComputer
				+ "' ORDER BY numberOfScanversion";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				numberOfScanversion = rs.getInt("numberOfScanversion");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return numberOfScanversion;
	}

	public int insertOSIntoDB(OperatingSystem os) {
		String nameOperatingSystem = os.getNameOperatingSystem();
		String buildOperatingSystem = os.getBuildOperatingSystem();
		String architecture32Bit64BitOperatingSystem = os
				.getArchitecture32Bit64BitOperatingSystem();
		int idOs = checkIfOSExistsInDB(nameOperatingSystem, buildOperatingSystem,
				architecture32Bit64BitOperatingSystem);
		if (idOs == -1) {
			String query = "INSERT INTO operatingSystem "
					+ "(nameOperatingSystem, buildOperatingSystem, architecture32Bit64BitOperatingSystem) VALUES "
					+ "('" + nameOperatingSystem + "', '" + buildOperatingSystem + "', '"
					+ architecture32Bit64BitOperatingSystem + "')";
			this.dbconnector.updateQuery(query);
			this.dbconnector.closeResultSetAndPreparedStatement();
			idOs = this.dbconnector.getLastInsertedId();
		}
		return idOs;
	}

	public int checkIfOSExistsInDB(String nameOperatingSystem, String buildOperatingSystem,
			String architecture32Bit64BitOperatingSystem) {
		int idOs = -1;
		String selectQuery = "SELECT os.idOperatingSystem FROM operatingSystem os "
				+ "WHERE os.nameOperatingSystem = '" + nameOperatingSystem
				+ "' AND os.buildOperatingSystem = '" + buildOperatingSystem
				+ "' AND os.architecture32Bit64BitOperatingSystem = '"
				+ architecture32Bit64BitOperatingSystem + "'";
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idOs = rs.getInt("idOperatingSystem");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idOs;
	}

	public int insertComputerIntoDB(Computer computer, int idOs) {
		String hostNameComputer = computer.getHostNameComputer();
		String sidComputer = computer.getSidComputer();
		String ramComputer = computer.getRamComputer();
		String typeComputer = computer.getTypeComputer();
		int idOperatingSystem = idOs;
		String query = "INSERT INTO computer "
				+ "(hostNameComputer, sidComputer, ramComputer, typeComputer, operatingSystem_idOperatingSystem ) VALUES "
				+ "('" + hostNameComputer + "', '" + sidComputer + "', '" + ramComputer + "', '"
				+ typeComputer + "', " + idOperatingSystem + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();

		return this.dbconnector.getLastInsertedId();
	}

	public void insertScanversionHasComputer(int idScanversion, int idComputer) {
		String query = "INSERT INTO scanversion_has_computer "
				+ "(scanversion_idScanversion, computer_idComputer) VALUES " + "(" + idScanversion
				+ ", " + idComputer + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();
	}

	public void insertNics(List<Nic> nics, int idComputer) {
		for (int i = 0; i < nics.size(); i++) {
			Nic nic = nics.get(i);
			String nameNic = nic.getNameNic();
			String ipv4Nic = nic.getIpv4Nic();
			String ipv6Nic = nic.getIpv6Nic();
			String macAdressNic = nic.getMacAdressNic();
			String standardGatewayNic = nic.getStandardGatewayNic();
			String dhcpNic = new Boolean(nic.isDhcpNic()).toString();
			String subnetNic = nic.getSubnetNic();
			int idNic = checkIfNicExistsInDB(nameNic, ipv4Nic, ipv6Nic, macAdressNic,
					standardGatewayNic, dhcpNic, subnetNic);
			if (idNic == -1) {
				String query = "INSERT INTO nic "
						+ "(nameNic, ipv4Nic, ipv6Nic, macAdressNic, standardGatewayNic, dhcpNic, subnetNic) VALUES "
						+ "('" + nameNic + "', '" + ipv4Nic + "', '" + ipv6Nic + "', '"
						+ macAdressNic + "', '" + standardGatewayNic + "', '" + dhcpNic + "', '"
						+ subnetNic + "')";
				this.dbconnector.updateQuery(query);
				idNic = this.dbconnector.getLastInsertedId();
				this.dbconnector.closeResultSetAndPreparedStatement();
			}
			insertComputerHasNic(idComputer, idNic);
		}
	}

	public int checkIfNicExistsInDB(String nameNic, String ipv4Nic, String ipv6Nic,
			String macAdressNic, String standardGatewayNic, String dhcpNic, String subnetNic) {
		int idNic = -1;
		String selectQuery = "SELECT idNic FROM nic WHERE nameNic = '" + nameNic
				+ "' AND ipv4Nic = '" + ipv4Nic + "' AND ipv6Nic = '" + ipv6Nic
				+ "' AND macAdressNic = '" + macAdressNic + "' AND standardGatewayNic = '"
				+ standardGatewayNic + "' AND dhcpNic = '" + dhcpNic + "' AND subnetNic = '"
				+ subnetNic + "'";
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idNic = rs.getInt("idNic");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idNic;
	}

	public void insertComputerHasNic(int idComputer, int idNic) {
		String query = "INSERT INTO computer_has_nic " + "(computer_idComputer, nic_idNic) VALUES "
				+ "(" + idComputer + ", " + idNic + ")";
		this.dbconnector.updateQuery(query);
	}

	public void insertSoftwares(List<Software> softwares, int idComputer) {
		for (int i = 0; i < softwares.size(); i++) {
			Software software = softwares.get(i);
			String nameSoftware = software.getNameSoftware();
			String versionSoftware = software.getVersionSoftware();
			VendorSoftware vendorSoftware = software.getVendor();
			int idSoftware = checkIfSoftwareExistsInDB(nameSoftware, versionSoftware);
			if (idSoftware == -1) {
				String query = "INSERT INTO software " + "(nameSoftware, versionSoftware) VALUES "
						+ "('" + nameSoftware + "', '" + versionSoftware + "')";
				this.dbconnector.updateQuery(query);
				idSoftware = this.dbconnector.getLastInsertedId();
				this.dbconnector.closeResultSetAndPreparedStatement();
			}
			int idVendorSoftware = insertVendorSoftware(vendorSoftware);
			boolean softwareVendorPairExistsInDB = checkIfSoftwareVendorPairExistsInDB(idSoftware,
					idVendorSoftware);
			if (!softwareVendorPairExistsInDB) {
				insertSoftwareHasVendorSoftware(idSoftware, idVendorSoftware);
			}
			insertComputerHasSoftware(idComputer, idSoftware);
		}
	}

	public boolean checkIfSoftwareVendorPairExistsInDB(int idSoftware, int idVendorSoftware) {
		boolean softwareVendorPairExistsInDB = false;
		String checkQuery = "SELECT * FROM software_has_vendorSoftware WHERE software_idSoftware = "
				+ idSoftware + " AND vendorSoftware_idVendorSoftware = " + idVendorSoftware;
		ResultSet rs = this.dbconnector.executeQuery(checkQuery);
		try {
			while (rs.next()) {
				softwareVendorPairExistsInDB = true;
			}
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return softwareVendorPairExistsInDB;
	}

	public int checkIfSoftwareExistsInDB(String nameSoftware, String versionSoftware) {
		int idSoftware = -1;
		String selectQuery = "SELECT idSoftware FROM software WHERE nameSoftware = '"
				+ nameSoftware + "' AND versionSoftware = '" + versionSoftware + "'";
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idSoftware = rs.getInt("idSoftware");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idSoftware;
	}

	public int insertVendorSoftware(VendorSoftware vendorSoftware) {
		String nameVendorSoftware = vendorSoftware.getNameVendorSoftware();
		int idVendor = checkIfVendorExistsInDB(nameVendorSoftware);
		if (idVendor == -1) {
			String query = "INSERT INTO vendorSoftware " + "(nameVendorSoftware) VALUES " + "('"
					+ nameVendorSoftware + "')";
			this.dbconnector.updateQuery(query);
			idVendor = this.dbconnector.getLastInsertedId();
			this.dbconnector.closeResultSetAndPreparedStatement();
		}
		return idVendor;
	}

	public int checkIfVendorExistsInDB(String nameVendorSoftware) {
		int idVendor = -1;
		String selectQuery = "SELECT idVendorSoftware FROM vendorSoftware WHERE nameVendorSoftware = '"
				+ nameVendorSoftware + "'";
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idVendor = rs.getInt("idVendorSoftware");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idVendor;
	}

	public void insertSoftwareHasVendorSoftware(int idSoftware, int idVendorSoftware) {
		String query = "INSERT INTO software_has_vendorSoftware "
				+ "(software_idSoftware, vendorSoftware_idVendorSoftware) VALUES " + "("
				+ idSoftware + ", " + idVendorSoftware + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();
	}

	public void insertComputerHasSoftware(int idComputer, int idSoftware) {
		String query = "INSERT INTO computer_has_software "
				+ "(computer_idComputer, software_idSoftware) VALUES " + "(" + idComputer + ", "
				+ idSoftware + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();
	}

	public void insertPrinters(List<Printer> printers, int idComputer) {
		for (int i = 0; i < printers.size(); i++) {
			Printer printer = printers.get(i);
			String namePrinter = printer.getNamePrinter();
			String driverNamePrinter = printer.getDriverNamePrinter();
			String driverVersionPrinter = printer.getDriverVersionPrinter();
			int idPrinter = checkIfPrinterExistsInDB(namePrinter, driverNamePrinter,
					driverVersionPrinter);
			if (idPrinter == -1) {
				String query = "INSERT INTO printer "
						+ "(namePrinter, driverNamePrinter, driverVersionPrinter) VALUES " + "('"
						+ namePrinter + "', '" + driverNamePrinter + "', '" + driverVersionPrinter
						+ "')";
				this.dbconnector.updateQuery(query);
				idPrinter = this.dbconnector.getLastInsertedId();
				this.dbconnector.closeResultSetAndPreparedStatement();
			}
			insertComputerHasPrinter(idComputer, idPrinter);
		}

	}

	public int checkIfPrinterExistsInDB(String namePrinter, String driverNamePrinter,
			String driverVersionPrinter) {
		int idPrinter = -1;
		String selectQuery = "SELECT idPrinter FROM printer WHERE namePrinter = '" + namePrinter
				+ "' AND driverNamePrinter = '" + driverNamePrinter
				+ "' AND driverVersionPrinter = '" + driverVersionPrinter + "'";
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idPrinter = rs.getInt("idPrinter");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idPrinter;
	}

	public void insertComputerHasPrinter(int idComputer, int idPrinter) {
		String query = "INSERT INTO computer_has_printer "
				+ "(computer_idComputer, printer_idPrinter) VALUES " + "(" + idComputer + ", "
				+ idPrinter + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();
	}

	public int insertProcessor(Processor processor) {
		String nameProcessor = processor.getNameProcessor();
		String familyProcessor = processor.getFamilyProcessor();
		int coresProcessor = processor.getCoresProcessor();
		int clockProcessor = processor.getClockProcessor();
		int idProcessor = checkIfProcessorExistsInDB(nameProcessor, familyProcessor,
				coresProcessor, clockProcessor);
		if (idProcessor == -1) {
			String query = "INSERT INTO processor "
					+ "(nameProcessor, familyProcessor, coresProcessor, clockProcessor) VALUES "
					+ "('" + nameProcessor + "', '" + familyProcessor + "', " + coresProcessor
					+ ", " + clockProcessor + ")";
			this.dbconnector.updateQuery(query);
			idProcessor = this.dbconnector.getLastInsertedId();
			this.dbconnector.closeResultSetAndPreparedStatement();
		}
		return idProcessor;
	}

	public int checkIfProcessorExistsInDB(String nameProcessor, String familyProcessor,
			int coresProcessor, int clockProcessor) {
		int idProcessor = -1;
		String selectQuery = "SELECT idProcessor FROM processor WHERE nameProcessor = '"
				+ nameProcessor + "' AND familyProcessor = '" + familyProcessor
				+ "' AND coresProcessor = " + coresProcessor + " AND clockProcessor = "
				+ clockProcessor;
		ResultSet rs = this.dbconnector.executeQuery(selectQuery);
		try {
			while (rs.next()) {
				idProcessor = rs.getInt("idProcessor");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return idProcessor;
	}

	public void insertComputerHasProcessor(int idProcessor, int idComputer) {
		String query = "INSERT INTO computer_has_processor "
				+ "(computer_idComputer, processor_idProcessor) VALUES " + "(" + idComputer + ", "
				+ idProcessor + ")";
		this.dbconnector.updateQuery(query);
		this.dbconnector.closeResultSetAndPreparedStatement();
	}

}
